{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import json\n",
    "target_path = r'C:\\Users\\18027\\Desktop\\工作任务\\FinalShell4.3.10'\n",
    "rs_df = pd.DataFrame()\n",
    "def get_ip_info(ip_address):  \n",
    "    # 调用 ip-api.com API 获取 IP 信息  \n",
    "    try:  \n",
    "        response = requests.get(f'http://ip-api.com/json/{ip_address}?lang=zh-CN')  \n",
    "        response.raise_for_status()  # 检查请求是否成功  \n",
    "        ip_info = response.json()  \n",
    "        \n",
    "        # 检查 API 返回的状态  \n",
    "        if ip_info['status'] == 'fail':  \n",
    "            return f\"无法获取 IP 地址 {ip_address} 的信息: {ip_info['message']}\"  \n",
    "        \n",
    "        return ip_info  \n",
    "    except requests.exceptions.HTTPError as http_err:  \n",
    "        return f\"HTTP 错误: {http_err}\"  \n",
    "    except Exception as err:  \n",
    "        return f\"发生错误: {err}\" \n",
    "for root, dirs, files in os.walk(target_path):\n",
    "    for file in files:\n",
    "        if file.endswith('.json'):\n",
    "            source_path = os.path.join(root, file)\n",
    "            print(source_path)\n",
    "            with open(source_path, 'r', encoding='utf-8') as f:\n",
    "                data = json.load(f)\n",
    "                # 获取host ip地址\n",
    "                print()\n",
    "                ip_address = data.get('host')\n",
    "                ip_info = get_ip_info(ip_address)\n",
    "\n",
    "                #print(data)\n",
    "                df = pd.json_normalize(data)\n",
    "                if isinstance(ip_info,dict):\n",
    "                    df['ip是否有效'] = '是'\n",
    "                    df['归属地'] = ip_info.get(\"city\")\n",
    "                    df['地区'] = ip_info.get('regionName')\n",
    "                    df['国家'] = ip_info.get('country')\n",
    "                else:\n",
    "                    df['ip是否有效'] = '否'\n",
    "                    df['归属地'] = ''\n",
    "                    df['地区'] = ''\n",
    "                    df['国家'] = ''\n",
    "                df['文件名称'] = file\n",
    "                df['数据来源'] = source_path\n",
    "                rs_df = pd.concat([rs_df,df])\n",
    "            #     #df.to_excel(os.path.join(root, file.replace('.json', '.xlsx')))\n",
    "            #     #print(f'Successfully converted {file} to xlsx.')\n",
    "\n",
    "            #     #break\n",
    "# 文件名称\t数据来源\n",
    "rs_df_part = rs_df[['host','ip是否有效','归属地','地区','国家','user_name','password','parent_id','secret_key_id','文件名称','数据来源']]\n",
    "output_file_path = os.path.join(target_path,'服务器ip提取数据.xlsx')\n",
    "\n",
    "rs_df.to_excel(r'C:\\Users\\18027\\Desktop\\工作任务\\服务器提取数据.xlsx',index=False)\n",
    "rs_df_part.to_excel(r'C:\\Users\\18027\\Desktop\\工作任务\\服务器ip提取数据.xlsx',index=False)\n",
    "\n",
    "# with pd.ExcelWriter(output_file_path) as writer:\n",
    "#     rs_df.to_excel(output_file_path,sheet_name='1',index=False)\n",
    "#     rs_df_part.to_excel(output_file_path,sheet_name='2',index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests  \n",
    "\n",
    "def get_ip_info(ip_address):  \n",
    "    # 调用 ip-api.com API 获取 IP 信息  \n",
    "    try:  \n",
    "        response = requests.get(f'http://ip-api.com/json/{ip_address}?lang=zh-CN')  \n",
    "        response.raise_for_status()  # 检查请求是否成功  \n",
    "        ip_info = response.json()  \n",
    "        \n",
    "        # 检查 API 返回的状态  \n",
    "        if ip_info['status'] == 'fail':  \n",
    "            return f\"无法获取 IP 地址 {ip_address} 的信息: {ip_info['message']}\"  \n",
    "        \n",
    "        return ip_info  \n",
    "    except requests.exceptions.HTTPError as http_err:  \n",
    "        return f\"HTTP 错误: {http_err}\"  \n",
    "    except Exception as err:  \n",
    "        return f\"发生错误: {err}\"  \n",
    "\n",
    "if __name__ == \"__main__\":  \n",
    "    ip_address = '103.172.41.164' \n",
    "    info = get_ip_info(ip_address)  \n",
    "    \n",
    "    # 输出获取的 IP 信息  \n",
    "    if isinstance(info, dict):  \n",
    "        print(f\"IP: {info.get('query')}\")  \n",
    "        print(f\"城市: {info.get('city')}\")  \n",
    "        print(f\"地区: {info.get('regionName')}\")  \n",
    "        print(f\"国家: {info.get('country')}\")  \n",
    "        print(f\"邮政编码: {info.get('zip')}\")  \n",
    "        print(f\"ISP: {info.get('isp')}\")  \n",
    "    else:  \n",
    "        print(info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# json_normalize函数使用\n",
    "# 参数：data,record_paht: json path,record_prefix: 前缀，meta: 元数组\n",
    "import pandas as pd  \n",
    "import json\n",
    "def json_to_excel( excel_file):  \n",
    "    try:  \n",
    "        # 读取 JSON 文件  \n",
    "        data = [\n",
    "            {\n",
    "                \"state\": \"Florida\",\n",
    "                \"shortname\": \"FL\",\n",
    "                \"info\": {\"governor\": \"Rick Scott\"},\n",
    "                \"counties\": [\n",
    "                    {\"name\": \"Dade\", \"population\": 12345},\n",
    "                    {\"name\": \"Broward\", \"population\": 40000},\n",
    "                    {\"name\": \"Palm Beach\", \"population\": 60000},\n",
    "                ],\n",
    "            },\n",
    "            {\n",
    "                \"state\": \"Ohio\",\n",
    "                \"shortname\": \"OH\",\n",
    "                \"info\": {\"governor\": \"John Kasich\"},\n",
    "                \"counties\": [\n",
    "                    {\"name\": \"Summit\", \"population\": 1234},\n",
    "                    {\"name\": \"Cuyahoga\", \"population\": 1337},\n",
    "                ],\n",
    "            },]\n",
    "        #print(data)\n",
    "        # 使用 json_normalize 处理复杂 JSON  \n",
    "        df = pd.json_normalize(data,record_path=\"counties\",record_prefix='counties.',meta=[\"state\",\"shortname\",[\"info\",\"governor\"]])  \n",
    "\n",
    "        # 将 DataFrame 输出到 Excel 文件  \n",
    "        #df.to_excel(excel_file, index=False)  \n",
    "        print(df)\n",
    "        json_data = df.to_json(orient='records')\n",
    "        print(type(json_data))\n",
    "        print(json_data)\n",
    "        result = json.loads(json_data)\n",
    "        print(type(result))\n",
    "        print(json.dumps(result,indent=4))\n",
    "\n",
    "        print(f\"成功将 {json_file} 转换为 {excel_file}\")  \n",
    "\n",
    "    except FileNotFoundError:  \n",
    "        print(f\"文件未找到: {json_file}\")  \n",
    "    except json.JSONDecodeError:  \n",
    "        print(\"JSON 解码错误: 请检查 JSON 文件格式\")  \n",
    "    except ValueError as e:  \n",
    "        print(f\"发生 ValueError: {e}\")  \n",
    "    except Exception as e:  \n",
    "        print(f\"发生错误: {e}\")  \n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":  \n",
    "    json_file = r'C:\\Users\\18027\\Desktop\\工作任务\\FinalShell4.3.10\\61.172.229.23_connect_config.json'   # 输入你的 JSON 文件名  \n",
    "    excel_file = './output.xlsx'  # 输出的 Excel 文件名  \n",
    "    json_to_excel(excel_file)  \n",
    "    print(f\"成功将 {json_file} 转换为 {excel_file}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      login_ip      country         province             city         area  \\\n",
      "0  192.168.1.1  China China  Beijing Beijing  Haidian Haidian  Area1 Area1   \n",
      "1  192.168.1.2  China China  Beijing Beijing  Haidian Haidian  Area1 Area1   \n",
      "2  192.168.1.3          USA       California    San Francisco        Area2   \n",
      "\n",
      "         isp  \n",
      "0  ISP1 ISP2  \n",
      "1  ISP1 ISP1  \n",
      "2       ISP3  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd  \n",
    "\n",
    "# 创建示例数据  \n",
    "data = {  \n",
    "    'login_ip': ['192.168.1.1', '192.168.1.2', '192.168.1.1', '192.168.1.3', '192.168.1.2'],  \n",
    "    'country': ['China', 'China', 'China', 'USA', 'China'],  \n",
    "    'province': ['Beijing', 'Beijing', 'Beijing', 'California', 'Beijing'],  \n",
    "    'city': ['Haidian', 'Haidian', 'Haidian', 'San Francisco', 'Haidian'],  \n",
    "    'area': ['Area1', 'Area1', 'Area1', 'Area2', 'Area1'],  \n",
    "    'isp': ['ISP1', 'ISP1', 'ISP2', 'ISP3', 'ISP1']  \n",
    "}  \n",
    "\n",
    "# 创建 DataFrame  \n",
    "df = pd.DataFrame(data)  \n",
    "\n",
    "# 使用 groupby 对 'login_ip' 进行分组，并合并其他列  \n",
    "result = df.groupby('login_ip').agg(  \n",
    "    country=('country', ' '.join),  \n",
    "    province=('province', ' '.join),  \n",
    "    city=('city', ' '.join),  \n",
    "    area=('area', ' '.join),  \n",
    "    isp=('isp', ' '.join)  \n",
    ").reset_index()  \n",
    "\n",
    "# 显示结果  \n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      login_ip country    province           city   area   isp  \\\n",
      "0  192.168.1.1   China     Beijing        Haidian  Area1  ISP1   \n",
      "1  192.168.1.2   China     Beijing        Haidian  Area1  ISP2   \n",
      "2  192.168.1.3     USA  California  San Francisco  Area2  ISP3   \n",
      "\n",
      "                             combined_info  \n",
      "0         China Beijing Haidian Area1 ISP1  \n",
      "1         China Beijing Haidian Area1 ISP2  \n",
      "2  USA California San Francisco Area2 ISP3  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd  \n",
    "\n",
    "# 创建示例数据  \n",
    "data = {  \n",
    "    'login_ip': ['192.168.1.1', '192.168.1.2', '192.168.1.3'],  \n",
    "    'country': ['China', 'China', 'USA'],  \n",
    "    'province': ['Beijing', 'Beijing', 'California'],  \n",
    "    'city': ['Haidian', 'Haidian', 'San Francisco'],  \n",
    "    'area': ['Area1', 'Area1', 'Area2'],  \n",
    "    'isp': ['ISP1', 'ISP2', 'ISP3']  \n",
    "}  \n",
    "\n",
    "# 创建 DataFrame  \n",
    "df = pd.DataFrame(data)  \n",
    "\n",
    "# 将多列内容合并为新列 'combined_info'  \n",
    "df['combined_info'] = df[['country', 'province', 'city', 'area', 'isp']].agg(' '.join, axis=1)  \n",
    "\n",
    "# 显示结果  \n",
    "print(df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
